import java.sql.*;
import java.util.Scanner;

import static java.lang.System.exit;
import static java.time.temporal.TemporalAdjusters.next;

public class first {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        int shu =7;
        while (shu!=0) {
            System.out.println("1.查看表");
            System.out.println("2.添加数据");
            System.out.println("3.删除数据");
            System.out.println("4.更新数据");
            System.out.println("5.查找数据");
            System.out.println("6.关闭程序");
            shu = sc.nextInt();
            switch (shu) {
                case 1:
                    showdatabase();
                    break;
                case 2:
                    System.out.println("请输入name");
                    String a = sc.next();
                    System.out.println("请输入pass");
                    String b = sc.next();
                    add(a,b);
                    break;
                case 3:
                    System.out.println("请输入要删除的id");
                    int c = sc.nextInt();
                    detele(c);
                    break;
                case 4:
                    System.out.println("请输出需要更新的数据id和更新后的name和pass");
                    int id1 = sc.nextInt();
                    String name1 = sc.next();
                    String pass1 = sc.next();
                    update(id1, pass1, name1);
                    break;
                case 5:
                    System.out.println("请输入要查询的数据的名字");
                    String name2 = sc.next();
                    select(name2);
                    break;
                case 6:
                    exit(0);
            }
        }
    }

        public static void showdatabase( ){
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
                String username = "root";
                String password = "989898";
                String url = "jdbc:mysql://localhost:3306/jdbc";
                // 建立连接
                Connection connection = DriverManager.getConnection(url, username, password);
                // 创建SQL语句执行对象
                Statement statement = connection.createStatement();
                // 执行查询语句
                ResultSet resultSet = statement.executeQuery("SELECT * FROM sys_user");

                while (resultSet.next()) {
                    // 根据列名获取数据，这里假设表中有id和name两列
                    int id = resultSet.getInt("id");
                    String name = resultSet.getString("name");
                    String pass = resultSet.getString("pass");
                    System.out.println("id: " + id + ", name: " + name + ", pass: " + pass);
                }

                // 关闭结果集、语句和连接
                resultSet.close();
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                throw new RuntimeException(e);
            }
        }
        public static void add(String newname,String newpass){
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
                String username = "root";
                String password = "989898";
                String url = "jdbc:mysql://localhost:3306/jdbc";
                // 建立连接
                Connection conn= DriverManager.getConnection(url, username, password);
                // 创建SQL语句执行对象
                String sql = "insert into sys_user(name,pass) values (?,?) ";
                PreparedStatement preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setObject(1,newname);
                preparedStatement.setObject(2,newpass);
                int result = preparedStatement.executeUpdate();
                if(result > 0){
                    System.out.println("新增成功"+newname);

                }else{
                    System.out.println("失败");
                }
            } catch (SQLException | ClassNotFoundException e) {
                e.printStackTrace();
            }
        }
        public static void detele(int c){
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
                String username = "root";
                String password = "989898";
                String url = "jdbc:mysql://localhost:3306/jdbc";
                // 建立连接
                Connection conn= DriverManager.getConnection(url, username, password);
                // 创建SQL语句执行对象
                String sql = "delete from sys_user where id=?";
                PreparedStatement preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setInt(1,c);
                int result = preparedStatement.executeUpdate();
                if(result > 0){
                    System.out.println("删除成功");
                }else{
                    System.out.println("删除失败");
                }
            } catch (SQLException | ClassNotFoundException e) {
                e.printStackTrace();
            }

        }
    public static void update(int id,String newpass,String newname){
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String username = "root";
            String password = "989898";
            String url = "jdbc:mysql://localhost:3306/jdbc";
            // 建立连接
            Connection conn= DriverManager.getConnection(url, username, password);
            // 创建SQL语句执行对象
            String sql = "update sys_user set name=?,pass=? where id=?";
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setObject(1,newname);
            preparedStatement.setObject(2,newpass);
            preparedStatement.setObject(3,id);
            int result = preparedStatement.executeUpdate();
            if(result > 0){
                System.out.println("更新成功");
            }else{
                System.out.println("更新失败");
            }
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        }

    }
    public static void select(String name1){
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String username = "root";
            String password = "989898";
            String url = "jdbc:mysql://localhost:3306/jdbc";
            // 建立连接
            Connection conn= DriverManager.getConnection(url, username, password);
            // 创建SQL语句执行对象
            String sql = "select * from sys_user where name=?";
            PreparedStatement preparedStatement = conn.prepareStatement(sql);

            preparedStatement.setObject(1,name1);
            ResultSet resultSet=preparedStatement.executeQuery();
            while (resultSet.next()) {
                // 根据列名获取数据，这里假设表中有id和name两列
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                System.out.println("id: " + id + ", name: " + name+"pass:");
            }
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        }

    }
        }


